const express = require("express");
const router = express.Router();
const db = require("../sql");
const types = require("../types");
const { v4: uuidv4 } = require("uuid"); // 引入UUID生成库来帮助生成唯一码
// 创建订单编码
function generateOrderCode() {
  const date = new Date();
  const year = date.getFullYear();
  const month = ("0" + (date.getMonth() + 1)).slice(-2);
  const day = ("0" + date.getDate()).slice(-2);
  const uuid = uuidv4().slice(0, 8); // 使用UUID生成一段短码
  return `ORD${year}${month}${day}${uuid}`;
}

// 创建订单
router.post("/add", async (req, res) => {
  const { userId, items, totalPrice, addressId } = req.body; // 假设前端传递的items为包含productId和quantity和price的数组
  const code = generateOrderCode();
  try {
    if (items.length === 0) {
      return res
        .status(200)
        .send({ success: false, message: "订单中没有商品" });
    }
    const result = await db.query(
      "INSERT INTO orderForm (userId, code, totalPrice, status,addressId) VALUES (?, ?, ?, ?,?)",
      [userId, code, totalPrice, types.ORDER_STATUS.WAIT_DELIVERY, addressId]
    );
    const orderId = result.insertId;

    // 插入订单明细
    const insertItems = items.map((item) =>
      db.query(
        "INSERT INTO orderItems (orderId, productId, quantity, price) VALUES (?, ?, ?, ?)",
        [orderId, item.productId, item.quantity, item.price]
      )
    );
    await Promise.all(insertItems);

    res
      .status(201)
      .send({ success: true, message: "Order created", data: { code: code } });
  } catch (error) {
    res.status(500).send({ message: "Error creating order", error });
  }
});

// 删除订单
router.delete("/:id", async (req, res) => {
  const { id } = req.params;
  try {
    await db.query("DELETE FROM orderForm WHERE id = ?", [id]);
    res.status(200).send({ message: "Order deleted" });
  } catch (error) {
    res.status(500).send({ message: "Error deleting order", error });
  }
});

// 查询订单列表（分页）并关联用户信息
router.get("/getAll", async (req, res) => {
  try {
    const { page = 1, limit = 10 } = req.query;
    const offset = (page - 1) * limit;

    // 获取订单基本信息，并关联用户信息和订单明细（商品），使用订单时的价格和数量，商品的最新名称和图片
    const ordersQuery = `
          SELECT o.id, o.code,o.expressCode, o.totalPrice, o.status, o.updateTime, o.createTime, a.contacts, a.address, a.phone,
          (
              SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                      'productId', oi.productId,
                      'quantity', oi.quantity,
                      'price', oi.price,
                      'title', p.title,
                      'pic', p.pic
                  )
              )
              FROM orderItems oi
              JOIN product p ON oi.productId = p.id
              WHERE oi.orderId = o.id
          ) AS products,
          JSON_OBJECT(
              'contacts', a.contacts,
              'address', a.address,
              'phone', a.phone,
              'isDefault', a.isDefault
          ) AS deliveryAddress
      FROM orderForm o
      LEFT JOIN address a ON o.addressId = a.id
      LIMIT ? OFFSET ?;`;
    const countQuery = "SELECT COUNT(*) AS total FROM orderForm;";

    // 同时执行两个查询
    const [orders, total] = await Promise.all([
      db.query(ordersQuery, [parseInt(limit), parseInt(offset)]),
      db.query(countQuery),
    ]);

    const formattedOrders = orders.map((order) => ({
      ...order,
      products: JSON.parse(order.products || "[]"), // 确保空的情况下转化为空数组
      deliveryAddress: order.deliveryAddress
        ? JSON.parse(order.deliveryAddress)
        : null,
    }));

    // 计算总页数
    const totalPages = Math.ceil(total[0].total / limit);

    res.status(200).json({
      success: true,
      data: {
        orders: formattedOrders,
        currentPage: parseInt(page),
        totalPages,
        totalCount: total[0].total,
      },
    });
  } catch (error) {
    res
      .status(500)
      .json({ success: false, message: "服务器错误", error: error.message });
  }
});

// 根据userId查询订单列表
router.get("/user/:userId", async (req, res) => {
  try {
    const { userId } = req.params;
    const { status } = req.query; // 从查询参数中获取状态

    // 更新查询语句以包括来自address表的信息
    let ordersQuery = `
        SELECT o.id, o.code,expressCode, o.totalPrice, o.status, o.updateTime, o.createTime, a.contacts, a.address, a.phone,
            (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'productId', oi.productId,
                        'quantity', oi.quantity,
                        'price', oi.price,
                        'title', p.title,
                        'pic', p.pic
                    )
                )
                FROM orderItems oi
                JOIN product p ON oi.productId = p.id
                WHERE oi.orderId = o.id
            ) AS products,
            JSON_OBJECT(
                'contacts', a.contacts,
                'address', a.address,
                'phone', a.phone,
                'isDefault', a.isDefault
            ) AS deliveryAddress
        FROM orderForm o
        LEFT JOIN address a ON o.addressId = a.id
        WHERE o.userId = ?`;

    const queryParams = [userId];

    // 如果提供了status参数，增加到WHERE子句中
    if (status) {
      ordersQuery += ` AND o.status = ?`;
      queryParams.push(status);
    }

    ordersQuery += ` ORDER BY o.createTime DESC;`;

    // 执行查询
    const orders = await db.query(ordersQuery, queryParams);

    // 将订单中的产品信息从字符串解析为JSON对象
    const formattedOrders = orders.map((order) => ({
      ...order,
      products: JSON.parse(order.products || "[]"), // 确保空的情况下转化为空数组
      deliveryAddress: order.deliveryAddress
        ? JSON.parse(order.deliveryAddress)
        : null,
    }));

    res.json({ success: true, data: formattedOrders });
  } catch (error) {
    res
      .status(500)
      .json({ success: false, message: "Server Error", error: error.message });
  }
});

// 发货
router.put("/ship/:id", async (req, res) => {
  const { id } = req.params;
  let { expressCode } = req.body;
  try {
    const result = await db.query(
      "UPDATE orderForm SET status = ?,expressCode = ? WHERE id = ? AND status = ?",
      [
        types.ORDER_STATUS.DELIVERED,
        expressCode,
        id,
        types.ORDER_STATUS.WAIT_DELIVERY,
      ]
    );
    if (result.affectedRows > 0) {
      res.json({ success: true, message: "订单已发货" });
    } else {
      res
        .status(404)
        .json({ success: false, message: "订单未找到或状态不允许发货" });
    }
  } catch (error) {
    res
      .status(500)
      .json({ success: false, message: "服务器错误", error: error.message });
  }
});

router.put("/receive/:id", async (req, res) => {
  const { id } = req.params;
  try {
    const result = await db.query(
      "UPDATE orderForm SET status = ? WHERE id = ? AND status = ?",
      [types.ORDER_STATUS.FINISHED, id, types.ORDER_STATUS.DELIVERED]
    );
    if (result.affectedRows > 0) {
      res.json({ success: true, message: "订单已确认收货" });
    } else {
      res
        .status(404)
        .json({ success: false, message: "订单未找到或状态不允许确认收货" });
    }
  } catch (error) {
    res
      .status(500)
      .json({ success: false, message: "服务器错误", error: error.message });
  }
});

// 查询订单总数
router.get("/totalOrders", async (req, res) => {
  const sql = "SELECT COUNT(*) AS totalOrders FROM orderForm";
  try {
    const result = await db.query(sql);
    res.json({ data: result[0].totalOrders, success: true });
  } catch (err) {
    res.status(500).json({ error: err.message, success: false });
  }
});

// 查询订单金额总数
router.get("/totalPrice", async (req, res) => {
  const sql = "SELECT SUM(totalPrice) AS totalPriceSum FROM orderForm";
  try {
    const [result] = await db.query(sql);
    res.json({ data: result.totalPriceSum || 0, success: true }); // 如果没有订单，确保返回0而不是null
  } catch (err) {
    res.status(500).json({ error: err.message, success: false });
  }
});

router.get("/salesByDay", async (req, res) => {
  const sql = `
      SELECT 
        DATE_FORMAT(date_sequence.date, '%Y-%m-%d') AS day,
        IFNULL(SUM(o.totalPrice), 0) AS dailySales
      FROM (
        SELECT CURDATE() - INTERVAL seq DAY AS date
        FROM (
          SELECT a.a + (10 * b.a) + (100 * c.a) AS seq
          FROM (
            SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) a
          CROSS JOIN (
            SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) b
          CROSS JOIN (
            SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) c
        ) a
        WHERE seq < 30
      ) date_sequence
      LEFT JOIN orderForm o ON DATE(o.createTime) = date_sequence.date
      GROUP BY date_sequence.date
      ORDER BY date_sequence.date ASC;
  `;
  try {
    const rows = await db.query(sql); // 获取行数据
    const data = rows.map((item) => ({
      day: item.day,
      dailySales: item.dailySales,
    }));
    res.json({ data: data, success: true });
  } catch (err) {
    res.status(500).json({ error: err.message, success: false });
  }
});

module.exports = router;
